/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2021/11/24 10:10:27                          */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('MedicalRecords') and o.name = 'FK_MEDICALR_REFERENCE_RECORDSE')
alter table MedicalRecords
   drop constraint FK_MEDICALR_REFERENCE_RECORDSE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('MedicalRecords') and o.name = 'FK_MEDICALR_REFERENCE_MEDICINE')
alter table MedicalRecords
   drop constraint FK_MEDICALR_REFERENCE_MEDICINE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('RecordSet') and o.name = 'FK_RECORDSE_RECORDSET_PATIENTS')
alter table RecordSet
   drop constraint FK_RECORDSE_RECORDSET_PATIENTS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('RecordSet') and o.name = 'FK_RECORDSE_REFERENCE_WORKERS')
alter table RecordSet
   drop constraint FK_RECORDSE_REFERENCE_WORKERS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Workers') and o.name = 'FK_WORKERS_REFERENCE_DEPARTME')
alter table Workers
   drop constraint FK_WORKERS_REFERENCE_DEPARTME
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Department')
            and   type = 'U')
   drop table Department
go

if exists (select 1
            from  sysobjects
           where  id = object_id('MedicalRecords')
            and   type = 'U')
   drop table MedicalRecords
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Medicines')
            and   type = 'U')
   drop table Medicines
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Patients')
            and   type = 'U')
   drop table Patients
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RecordSet')
            and   type = 'U')
   drop table RecordSet
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Workers')
            and   type = 'U')
   drop table Workers
go

/*==============================================================*/
/* Table: Department                                            */
/*==============================================================*/
create table Department (
   DepartmentID         char(4)              not null,
   DepartmentName       varchar(10)          not null,
   constraint PK_DEPARTMENT primary key (DepartmentID)
)
go

/*==============================================================*/
/* Table: MedicalRecords                                        */
/*==============================================================*/
create table MedicalRecords (
   RecordID             int                  identity,
   RecordSetID          int                  not null,
   MedicineID           int                  not null,
   MedicineAmount       int                  not null,
   constraint PK_MEDICALRECORDS primary key (RecordID)
)
go

/*==============================================================*/
/* Table: Medicines                                             */
/*==============================================================*/
create table Medicines (
   MedicineID           int                  identity,
   Stock                int                  not null,
   Price                money                not null,
   constraint PK_MEDICINES primary key (MedicineID)
)
go

/*==============================================================*/
/* Table: Patients                                              */
/*==============================================================*/
create table Patients (
   PatientID            int                  identity,
   PatientName          varchar(50)          not null,
   SignDate             datetime             null,
   Password             varchar(50)          not null,
   Phone                varchar(20)          null,
   constraint PK_PATIENTS primary key (PatientID)
)
go

/*==============================================================*/
/* Table: RecordSet                                             */
/*==============================================================*/
create table RecordSet (
   RecordSetID          int                  identity,
   RecordDate           datetime             null,
   WorkerID             int                  not null,
   PatientID            int                  not null,
   Describe             text                 null,
   constraint PK_RECORDSET primary key (RecordSetID)
)
go

/*==============================================================*/
/* Table: Workers                                               */
/*==============================================================*/
create table Workers (
   WorkerID             int                  identity,
   WorkerName           varchar(50)          not null,
   WorkerSalary         money                null,
   DepartmentID         char(4)              null,
   Password             varchar(50)          not null,
   Phone                varchar(20)          null,
   Type                 varchar(50)          null,
   constraint PK_WORKERS primary key (WorkerID)
)
go

alter table MedicalRecords
   add constraint FK_MEDICALR_REFERENCE_RECORDSE foreign key (RecordSetID)
      references RecordSet (RecordSetID)
go

alter table MedicalRecords
   add constraint FK_MEDICALR_REFERENCE_MEDICINE foreign key (MedicineID)
      references Medicines (MedicineID)
go

alter table RecordSet
   add constraint FK_RECORDSE_RECORDSET_PATIENTS foreign key (PatientID)
      references Patients (PatientID)
go

alter table RecordSet
   add constraint FK_RECORDSE_REFERENCE_WORKERS foreign key (WorkerID)
      references Workers (WorkerID)
go

alter table Workers
   add constraint FK_WORKERS_REFERENCE_DEPARTME foreign key (DepartmentID)
      references Department (DepartmentID)
go

